package zhou.chs.p3_3;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.TextView;

public class MainActivity extends AppCompatActivity {
    TextView tv;    //声明1个TextView控件显示数据库设备台账数据表内容
    static final String db_name="mydb";   //定义数据库名称
    static final String tb1="EP";          //定义设备台账数据表名称
    SQLiteDatabase db;              //声明1个SQLiteDatabase
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        tv = (TextView) findViewById (R.id.idtv);
        //创建数据库
        db=openOrCreateDatabase(db_name, Context.MODE_PRIVATE,null);
        //设备台账数据表的SQL语句
        //设备名称-name	 工艺位号-seq	设备ID-id	 报警温度-th
        String createTable="CREATE TABLE IF NOT EXISTS " + tb1 + "(name VARCHAR(20),seq VARCHAR(10),id VARCHAR(10),th INTEGER)";
        //创建设备台账数据表
        db.execSQL(createTable);
        //查询数据表
        Cursor cp = db.query(tb1, null, null, null, null, null, null);
        if(cp.getCount()==0) {
            //向设备台账数据表添加4条记录
            addtb1("1#进料泵", "P3101A", "E765ACEB", 90);
            addtb1("2#进料泵", "P3101B", "550E86D4", 90);
            addtb1("引风机", "F3201", "1767B9EB", 80);
            addtb1("润滑油泵", "C201", "761E1BB6", 80);
        }
        fresh();
    }
    //向设备台账数据表添加记录
    private void addtb1(String name, String seq, String id, int th) {
        ContentValues cv=new ContentValues(4); //定义ContentValues，存放4个数据
        cv.put("name", name);   //存放记录数据
        cv.put("seq", seq);
        cv.put("id", id);
        cv.put("th", th);
        db.insert(tb1, null, cv);  //插入记录
    }
    //刷新记录
    private void fresh(){
        //读取数据并显示
        Cursor cp = db.query(tb1, null, null, null, null, null, null);
        int sum = cp.getCount();
        cp.moveToFirst();
        String st;
        st = "设备台账数据表有" + sum + "条记录\n\n";
        for (int i = 0; i < sum; i++) {
            st=st+cp.getString(0)+"  "+cp.getString(1)+"  "+cp.getString(2)+"  "+cp.getInt(3)+"\n";
            cp.moveToNext();
        }
        tv.setText(st);
    }
    //删除记录
    public void rd(View view) {
        //删除设备名称为"1#进料泵"的记录
        //db.delete(tb1, "name=?", new String[]{"1#进料泵"});
        //删除报警温度＜90的记录
        //db.delete(tb1, "th<?", new String[]{"90"});
        //删除设备名称为"1#进料泵"和报警温度＜90的记录
        //db.delete(tb1, "name=? OR th<?", new String[]{"1#进料泵","90"});
        //用SQL语句删除报警温度＜90的记录
        db.execSQL("DELETE FROM " + tb1 +" WHERE th<90");
        fresh();
    }
    //修改记录
    public void ru(View view) {
        //ContentValues cv = new ContentValues();
        //cv.put("th",80);    //在values中添加内容
        //将"1#进料泵"的报警温度改为80
        //db.update(tb1,cv, "name=?", new String[]{"1#进料泵"});
        //将报警温度值为90的记录的报警值改为80
        //db.update(tb1,cv, "th=?", new String[]{"90"});
        //用SQL语句将报警温度值为90的记录的报警值改为80
        db.execSQL("UPDATE " + tb1 +" SET th=80 WHERE th=90");
        fresh();
    }
    //查询记录
    public void rs(View view) {
        //查询表内全部记录，返回全部字段
        //Cursor cp = db.query(tb1, null, null, null, null, null, null);
        //查询表内温度报警值小于90的记录，返回全部字段
        //Cursor cp = db.query(tb1, null, "th<?", new String[]{"90"}, null, null, null);
        //查询表内温度报警值小于90的记录，返回前2个字段
        //Cursor cp = db.query(tb1, new String[]{"name","seq"}, "th<?", new String[]{"90"}, null, null, null);
        //用rawQuery查询表内温度报警值小于90的记录，返回全部字段
        Cursor cp=db.rawQuery("SELECT * FROM " + tb1 + " WHERE th<?",new String[]{"90"});

        int sum = cp.getCount();
        cp.moveToFirst();
        String st;
        st = "设备台账数据表有" + sum + "条记录\n\n";
        for (int i = 0; i < sum; i++) {
            st=st+cp.getString(0)+"  "+cp.getString(1)+"  "+cp.getString(2)+"  "+cp.getInt(3)+"\n";
            //st=st+cp.getString(0)+"  "+cp.getString(1)+"\n";
            cp.moveToNext();
        }
        tv.setText(st);
    }
}
